#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据大屏 CRUD 层
负责数据库查询操作，不包含业务逻辑
"""
from datetime import datetime
from typing import List, Dict, Any, Tuple, Optional

from common.database import DatabaseService
from common.exceptions import BizException
from common.logger import create_logger

logger = create_logger(__name__)


class DashboardCRUD:
	"""数据大屏CRUD操作"""

	@staticmethod
	async def get_devices_in_region(db: DatabaseService, region_prefix: Optional[str] = None) -> List[Tuple]:
		"""
		根据区域编码前缀获取设备编码列表

		Args:
			db: 数据库服务
			region_prefix: 区域编码前缀

		Returns:
			List[Tuple]: 设备类型、设备ID和设备编码的元组列表
		"""
		try:
			where_clause = ""
			params = []

			if region_prefix:
				where_clause = "AND da.region_code LIKE %s"
				params.append(f"{region_prefix}%")

			sql = f"""
				SELECT da.device_type, da.device_id,
					CASE da.device_type
						WHEN 'METER' THEN m.code
						WHEN 'RTU' THEN r.serial_no
						WHEN 'BOX' THEN b.serial_no
						WHEN 'STATION' THEN s.code
					END AS serial_no
				FROM dev_device_address da
				LEFT JOIN dev_meter m ON da.device_type = 'METER' AND da.device_id = m.id
				LEFT JOIN dev_rtu r ON da.device_type = 'RTU' AND da.device_id = r.id
				LEFT JOIN dev_box b ON da.device_type = 'BOX' AND da.device_id = b.id
				LEFT JOIN dev_station s ON da.device_type = 'STATION' AND da.device_id = s.id
				WHERE da.is_primary = 1 AND da.deleted = 0 {where_clause}
			"""

			results = db.get_all(sql, params)
			return results or []

		except Exception as e:
			logger.error(f"获取区域内设备编码失败: {e}")
			return []

	@staticmethod
	async def count_open_alarms_by_meter_codes(db: DatabaseService,
											   region_prefix: Optional[str] = None,
											   start_time: Optional[datetime] = None,
											   end_time: Optional[datetime] = None,
											   station_id: Optional[int] = None,
											   ) -> int:
		"""
		统计指定区域中未处理告警数量

		Args:
			db: 数据库服务
			region_prefix: 区域编码前缀
			start_time: 开始时间
			end_time: 结束时间
			station_id: 台区ID

		Returns:
			int: 未处理告警数量
		"""
		try:
			where_conditions = ["a.status = 'OPEN'"]
			params = []

			# 区域条件
			if region_prefix:
				where_conditions.append("da.region_code LIKE %s")
				params.append(f"{region_prefix}%")

			if station_id:
				where_conditions.append("v.station_id = %s")
				params.append(station_id)

			if start_time:
				where_conditions.append("a.raised_at >= %s")
				params.append(start_time)
			if end_time:
				where_conditions.append("a.raised_at <= %s")
				params.append(end_time)

			where_clause = " AND ".join(where_conditions)

			sql = f"""
				SELECT COUNT(*) AS open_alarm_count
				FROM am_alarm a
				LEFT JOIN dev_device_address da ON da.device_code = a.meter_code AND da.is_primary = 1 AND da.deleted = 0
				INNER JOIN v_all_device v on v.serial_no = da.device_code and da.device_type = v.device_type
				WHERE {where_clause}
			"""

			result = db.get_one(sql, params)
			return result.get('open_alarm_count', 0) if result else 0

		except Exception as e:
			logger.error(f"统计未处理告警数量失败: {e}")
			return 0

	@staticmethod
	async def count_pending_work_orders_by_meter_codes(db: DatabaseService,
													   region_prefix: Optional[str] = None,
													   start_time: Optional[datetime] = None,
													   end_time: Optional[datetime] = None,
													   station_id: Optional[int] = None,
													   ) -> int:
		"""
		统计指定区域中未完成工单数量

		Args:
			db: 数据库服务
			region_prefix: 区域编码前缀
			start_time: 开始时间
			end_time: 结束时间
			station_id: 台区ID

		Returns:
			int: 未完成工单数量
		"""
		try:
			where_conditions = ["wo.status NOT IN ('CLOSED', 'RESOLVED', 'AUTO_CLOSED')"]
			params = []

			# 区域条件
			if region_prefix:
				where_conditions.append("da.region_code LIKE %s")
				params.append(f"{region_prefix}%")

			if station_id:
				where_conditions.append("v.station_id = %s")
				params.append(station_id)

			if start_time:
				where_conditions.append("a.raised_at >= %s")
				params.append(start_time)
			if end_time:
				where_conditions.append("a.raised_at <= %s")
				params.append(end_time)

			where_clause = " AND ".join(where_conditions)

			sql = f"""
				SELECT COUNT(*) AS pending_work_order_count
				FROM wo_work_order wo
				LEFT JOIN am_alarm a ON wo.alarm_id = a.id
				LEFT JOIN dev_device_address da ON da.device_code = a.meter_code AND da.is_primary = 1 AND da.deleted = 0
				LEFT JOIN v_all_device v on v.serial_no = da.device_code and da.device_type = v.device_type
				WHERE {where_clause}
			"""

			result = db.get_one(sql, params)
			return result.get('pending_work_order_count', 0) if result else 0

		except BizException as e:
			logger.error(f"统计未完成工单数量失败: {e}")
			return 0

	@staticmethod
	async def get_phase_meter_count(db: DatabaseService, station_id: Optional[int] = None) -> Dict[str, int]:
		"""
		获取分相电表总数统计（所有状态，含三相RTU）

		Args:
			db: 数据库服务
			station_id: 台区ID，可选

		Returns:
			Dict: {
				'phase_a_count': 100,  # A相电表总数（所有状态）
				'phase_b_count': 95,   # B相电表总数（所有状态）
				'phase_c_count': 98,   # C相电表总数（所有状态）
				'phase_three_count': 25  # 三相RTU设备数量（所有状态）
			}
		"""
		try:
			# 1. 统计单相电表（所有状态，不限制status）
			meter_where_conditions = [
				"m.deleted = 0",
				"m.meter_type = 'SINGLE'"  # 只统计单相表
			]
			meter_params = []

			# 按台区过滤
			if station_id:
				meter_where_conditions.append(
					"EXISTS (SELECT 1 FROM dev_rtu r WHERE r.id = m.rtu_id AND r.station_id = %s AND r.deleted = 0)"
				)
				meter_params.append(station_id)

			meter_where_clause = " AND ".join(meter_where_conditions)

			# 统计单相表各相数量
			meter_sql = f"""
				SELECT
					SUM(CASE WHEN m.phase = 'A' THEN 1 ELSE 0 END) as phase_a_count,
					SUM(CASE WHEN m.phase = 'B' THEN 1 ELSE 0 END) as phase_b_count,
					SUM(CASE WHEN m.phase = 'C' THEN 1 ELSE 0 END) as phase_c_count
				FROM dev_meter m
				WHERE {meter_where_clause}
			"""

			meter_result = db.get_one(meter_sql, meter_params)

			# 2. 统计三相RTU设备数量（所有状态，dev_rtu是真正的三相表）
			rtu_where_conditions = [
				"r.deleted = 0 AND r.collector_type = 'Master' "
			]
			rtu_params = []

			# 按台区过滤
			if station_id:
				rtu_where_conditions.append("r.station_id = %s")
				rtu_params.append(station_id)

			rtu_where_clause = " AND ".join(rtu_where_conditions)

			# 统计所有RTU设备数量（dev_rtu中的设备即为三相表）
			rtu_sql = f"""
				SELECT COUNT(*) as phase_three_count
				FROM dev_rtu r
				WHERE {rtu_where_clause}
			"""

			rtu_result = db.get_one(rtu_sql, rtu_params)

			# 合并结果
			if meter_result or rtu_result:
				return {
					'phase_a_count': (meter_result.get('phase_a_count') or 0) if meter_result else 0,
					'phase_b_count': (meter_result.get('phase_b_count') or 0) if meter_result else 0,
					'phase_c_count': (meter_result.get('phase_c_count') or 0) if meter_result else 0,
					'phase_three_count': (rtu_result.get('phase_three_count') or 0) if rtu_result else 0
				}
			else:
				return {
					'phase_a_count': 0,
					'phase_b_count': 0,
					'phase_c_count': 0,
					'phase_three_count': 0
				}

		except Exception as e:
			logger.error(f"获取分相电表统计失败: {e}")
			return {
				'phase_a_count': 0,
				'phase_b_count': 0,
				'phase_c_count': 0,
				'phase_three_count': 0
			}

	@staticmethod
	async def get_device_status_stats(db: DatabaseService, region_prefix: Optional[str] = None,
									  station_id: Optional[int] = None) -> Dict[str, Dict[str, int]]:
		"""
		获取设备状态统计

		Returns:
			Dict: {
				'station': {'total': 100, 'online': 95},
				'box': {'total': 500, 'online': 480},
				'rtu': {'total': 300, 'online': 290},
				'meter': {'total': 1000, 'abnormal': 12}
			}
		"""
		try:
			where = ""
			params = []
			box_where = ""
			box_params = []
			rtu_where = ""
			rtu_params = []
			meter_where = ""
			meter_params = []
			if region_prefix:  # 统一 LIKE
				where += " AND da.region_code LIKE %s"
				params.append(f"{region_prefix}%")

				box_where += " AND da.region_code LIKE %s"
				box_params.append(f"{region_prefix}%")

				rtu_where += " AND da.region_code LIKE %s"
				rtu_params.append(f"{region_prefix}%")

				meter_where += " AND da.region_code LIKE %s"
				meter_params.append(f"{region_prefix}%")

			if station_id:
				where += " AND s.id = %s"
				params.append(station_id)

				box_where += " AND b.station_id = %s"
				box_params.append(station_id)

				rtu_where += " AND r.station_id = %s"
				rtu_params.append(station_id)

				meter_where += " AND v.station_id = %s"
				meter_params.append(station_id)

			# 台区统计
			station_sql = f"""
					SELECT
						COUNT(*) as total,
						COALESCE(SUM(CASE WHEN s.status = 'ONLINE' THEN 1 ELSE 0 END), 0) as online
					FROM dev_station s
					INNER JOIN dev_device_address da ON da.device_type='STATION' AND s.id = da.device_id AND da.is_primary=1
					WHERE s.deleted = 0 {where}
				"""
			station_result = db.get_one(station_sql, params) or {'total': 0, 'online': 0}
			station_result['online'] = station_result.get('online') or 0

			# 电箱统计
			box_sql = f"""
					SELECT
						COUNT(*) as total,
						COALESCE(SUM(CASE WHEN b.status = 'ONLINE' THEN 1 ELSE 0 END), 0) as online
					FROM dev_box b
					INNER JOIN dev_device_address da ON da.device_type='BOX' AND b.id = da.device_id AND da.is_primary=1
					WHERE b.deleted = 0 {box_where}
				"""
			box_result = db.get_one(box_sql, box_params) or {'total': 0, 'online': 0}
			box_result['online'] = box_result.get('online') or 0

			# RTU统计
			rtu_sql = f"""
					SELECT
						COUNT(*) as total,
						COALESCE(SUM(CASE WHEN r.status = 'ONLINE' THEN 1 ELSE 0 END), 0) as online
					FROM dev_rtu r
					INNER JOIN dev_device_address da ON da.device_type='RTU' AND r.id = da.device_id AND da.is_primary=1
					WHERE r.deleted = 0 {rtu_where}
				"""
			rtu_result = db.get_one(rtu_sql, rtu_params) or {'total': 0, 'online': 0}
			rtu_result['online'] = rtu_result.get('online') or 0

			# 电表统计
			meter_sql = f"""
					SELECT
					  COUNT(DISTINCT v.serial_no) as total
					FROM v_all_device v
					INNER JOIN dev_device_address da ON  v.device_id = da.device_id AND da.is_primary=1
					WHERE v.device_type in ('METER') AND v.deleted = 0 {meter_where}
				"""
			meter_result = db.get_one(meter_sql, meter_params) or {'total': 0}

			# 电表异常统计
			error_meter_sql = f"""
					SELECT
						COALESCE(SUM(CASE WHEN v.status != 'ONLINE' THEN 1 ELSE 0 END), 0) as abnormal
					FROM v_all_device v
					INNER JOIN dev_device_address da ON da.device_type in ('METER','RTU','BOX') AND v.device_id = da.device_id AND da.is_primary=1 AND v.device_type = da.device_type
					WHERE v.deleted = 0 {meter_where}
				"""

			error_meter_result = db.get_one(error_meter_sql, meter_params) or {'abnormal': 0}
			meter_result['abnormal'] = error_meter_result.get('abnormal') or 0

			return {
				'station': station_result,
				'box': box_result,
				'rtu': rtu_result,
				'meter': meter_result
			}

		except Exception as e:
			logger.error(f"获取设备状态统计失败: {e}")
			return {
				'station': {'total': 0, 'online': 0},
				'box': {'total': 0, 'online': 0},
				'rtu': {'total': 0, 'online': 0},
				'meter': {'total': 0, 'abnormal': 0}
			}

	async def get_alarm_stats(self, db: DatabaseService,
							  region_prefix: Optional[str] = None,
							  start_time: Optional[datetime] = None,
							  end_time: Optional[datetime] = None,
							  station_id: Optional[int] = None,
							  ) -> Dict[str, int]:
		"""
		获取告警统计

		Args:
			db: 数据库服务
			region_prefix: 区域编码前缀
			start_time: 开始时间
			end_time: 结束时间
			station_id: 台区ID

		Returns:
			Dict: {'total': 8, 'critical': 2, 'warning': 6, 'info': 0}
		"""
		try:
			where_conditions = ["a.status = 'OPEN'"]
			params = []

			# 区域条件
			if region_prefix:
				where_conditions.append("da.region_code LIKE %s")
				params.append(f"{region_prefix}%")

			if station_id:
				where_conditions.append("v.station_id = %s")
				params.append(station_id)

			# 时间条件
			if start_time:
				where_conditions.append("a.raised_at >= %s")
				params.append(start_time)
			if end_time:
				where_conditions.append("a.raised_at <= %s")
				params.append(end_time)

			where_clause = " AND ".join(where_conditions)

			sql = f"""
                SELECT
                    COUNT(*) as total,
                    SUM(CASE WHEN ar.severity = 3 THEN 1 ELSE 0 END) as critical,
                    SUM(CASE WHEN ar.severity = 2 THEN 1 ELSE 0 END) as warning,
                    SUM(CASE WHEN ar.severity = 1 THEN 1 ELSE 0 END) as info
                FROM am_alarm a
                LEFT JOIN am_alarm_rule ar ON a.alarm_type = ar.rule_code
				LEFT JOIN dev_device_address da ON da.device_code = a.meter_code AND da.is_primary = 1 AND da.deleted = 0
				INNER JOIN v_all_device v on v.serial_no = da.device_code and da.device_type = v.device_type
                WHERE {where_clause}
            """
			result = db.get_one(sql, params)
			if result:
				return {
					'total': result.get('total', 0) or 0,
					'critical': result.get('critical', 0) or 0,
					'warning': result.get('warning', 0) or 0,
					'info': result.get('info', 0) or 0
				}
			return {'total': 0, 'critical': 0, 'warning': 0, 'info': 0}

		except Exception as e:
			logger.error(f"获取告警统计失败: {e}")
			return {'total': 0, 'critical': 0, 'warning': 0, 'info': 0}

	@staticmethod
	async def get_total_load_and_energy(db: DatabaseService, region_prefix: Optional[str] = None,
										station_id: Optional[int] = None) -> Tuple[
		float, float, float]:
		"""
		计算总负荷和供电量

		Args:
			db: 数据库服务
			region_prefix: 区域编码前缀
			station_id: 台区ID

		Returns:
			Tuple: (总负荷MW, 今日供电量万kWh, 昨日同比变化%)
		"""
		try:
			# 构建区域筛选条件
			where_conditions = []
			params = []

			if region_prefix:
				where_conditions.append("da.region_code LIKE %s")
				params.append(f"{region_prefix}%")
			if station_id:
				where_conditions.append("v.station_id = %s")
				params.append(station_id)

			where_clause = " AND ".join(where_conditions) if where_conditions else "1=1"
			where_clause = f" AND {where_clause}" if where_conditions else ""

			# 获取最新的总负荷 - 从最近1小时的数据中计算 MW：1000000，kw：1000，TODO:后续改为SUM(COALESCE(watt, 0)) / 1000000
			load_sql = f"""
                SELECT SUM(COALESCE(dt.watt, 0)) / 1000 as total_load_mw
                FROM dc_meter_telemetry dt
                LEFT JOIN dev_device_address da ON da.device_code = dt.meter_code AND da.is_primary = 1 AND da.deleted = 0
                INNER JOIN v_all_device v on v.serial_no = da.device_code and da.device_type = v.device_type
                WHERE dt.ts >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
                AND dt.quality = 'GOOD'
                AND dt.watt IS NOT NULL
                {where_clause}
            """
			load_result = db.get_one(load_sql, params)

			if load_result and load_result.get('total_load_mw', 0):
				total_load = float(load_result.get('total_load_mw', 0))
			else:
				total_load = 0.0

			# 今日累计供电量 万kwh：10000 TODO 后续应该换为万kwh，即：SUM(COALESCE(total_kwh, 0)) / 10000
			today_energy_sql = f"""
                SELECT SUM(COALESCE(t.total_kwh, 0)) as today_energy
                FROM (
                    SELECT dt.meter_code, MAX(dt.total_kwh) as total_kwh
                    FROM dc_meter_telemetry dt
                    LEFT JOIN dev_device_address da ON da.device_code = dt.meter_code AND da.is_primary = 1 AND da.deleted = 0
                    INNER JOIN v_all_device v on v.serial_no = da.device_code and da.device_type = v.device_type
                    WHERE DATE(dt.ts) = CURDATE()
                    AND dt.quality = 'GOOD'
                    AND dt.total_kwh IS NOT NULL
                    {where_clause}
                    GROUP BY dt.meter_code
                ) t
            """
			today_result = db.get_one(today_energy_sql, params)

			if today_result and today_result.get('today_energy', 0):
				today_energy = float(today_result.get('today_energy', 0))
			else:
				today_energy = 0.0

			# 昨日同期供电量 TODO 同上方若修改需同步改为：SUM(COALESCE(total_kwh, 0)) / 10000
			yesterday_energy_sql = f"""
                SELECT SUM(COALESCE(t.total_kwh, 0)) as yesterday_energy
                FROM (
                    SELECT dt.meter_code, MAX(dt.total_kwh) as total_kwh
                    FROM dc_meter_telemetry dt
                    LEFT JOIN dev_device_address da ON da.device_code = dt.meter_code AND da.is_primary = 1 AND da.deleted = 0
                    INNER JOIN v_all_device v on v.serial_no = da.device_code and da.device_type = v.device_type
                    WHERE DATE(dt.ts) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
                    AND dt.quality = 'GOOD'
                    AND dt.total_kwh IS NOT NULL
                    {where_clause}
                    GROUP BY dt.meter_code
                ) t
            """
			yesterday_result = db.get_one(yesterday_energy_sql, params)

			if yesterday_result and yesterday_result.get('yesterday_energy', 0):
				yesterday_energy = float(yesterday_result.get('yesterday_energy', 0))
			else:
				yesterday_energy = 0.0

			# 计算同比变化
			change_percent = 0.0
			if yesterday_energy > 0:
				change_percent = ((today_energy - yesterday_energy) / yesterday_energy) * 100

			return total_load, today_energy, change_percent

		except Exception as e:
			logger.error(f"计算总负荷和供电量失败: {e}")
			return 0.0, 0.0, 0.0

	@staticmethod
	async def get_high_load_stations(db: DatabaseService, limit: int = 10,
									 sort_by: str = "load_percentage",
									 order: str = "desc") -> List[Dict[str, Any]]:
		"""
		获取高负载台区列表

		Args:
			db: 数据库服务
			limit: 返回条数
			sort_by: 排序字段
			order: 排序方向

		Returns:
			List[Dict]: 台区负载列表
		"""
		try:
			# 构建排序子句
			order_clause = f"ORDER BY {sort_by} {order.upper()}"
			if sort_by not in ['load_percentage', 'alarm_count', 'load_kw']:
				order_clause = "ORDER BY load_percentage DESC"

			sql = f"""
                SELECT
                    s.id,
                    s.name,
                    s.code,
                    s.capacity_kva,
                    s.manager_code,
                    s.updated_at,
                    COALESCE(load_data.total_load_kw, 0) as load_kw,
                    CASE
                        WHEN s.capacity_kva > 0 THEN
                            ROUND((COALESCE(load_data.total_load_kw, 0) / s.capacity_kva) * 100, 1)
                        ELSE 0
                    END as load_percentage,
                    COALESCE(alarm_data.alarm_count, 0) as alarm_count,
                    CASE
                        WHEN COALESCE(load_data.total_load_kw, 0) / NULLIF(s.capacity_kva, 0) > 0.9 THEN '严重'
                        WHEN COALESCE(load_data.total_load_kw, 0) / NULLIF(s.capacity_kva, 0) > 0.85 THEN '预警'
                        ELSE '正常'
                    END as status,
                    CASE
                        WHEN COALESCE(load_data.total_load_kw, 0) / NULLIF(s.capacity_kva, 0) > 0.9 THEN 'red'
                        WHEN COALESCE(load_data.total_load_kw, 0) / NULLIF(s.capacity_kva, 0) > 0.85 THEN 'yellow'
                        ELSE 'green'
                    END as status_color
                FROM dev_station s
                LEFT JOIN (
                    SELECT
                        s2.id as station_id,
                        SUM(COALESCE(t.watt, 0)) / 1000 as total_load_kw
                    FROM dev_station s2
                    LEFT JOIN dev_box b ON s2.id = b.station_id
                    LEFT JOIN dev_rtu r ON b.id = r.box_id
                    LEFT JOIN dev_meter m ON r.id = m.rtu_id
                    LEFT JOIN (
                        SELECT
                            meter_code,
                            watt,
                            ROW_NUMBER() OVER (PARTITION BY meter_code ORDER BY ts DESC) as rn
                        FROM dc_meter_telemetry
                        WHERE ts >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
                        AND quality = 'GOOD'
                    ) t ON m.code = t.meter_code AND t.rn = 1
                    GROUP BY s2.id
                ) load_data ON s.id = load_data.station_id
                LEFT JOIN (
                    SELECT
                        s3.id as station_id,
                        COUNT(a.id) as alarm_count
                    FROM dev_station s3
                    LEFT JOIN dev_box b2 ON s3.id = b2.station_id
                    LEFT JOIN dev_rtu r2 ON b2.id = r2.box_id
                    LEFT JOIN dev_meter m2 ON r2.id = m2.rtu_id
                    LEFT JOIN am_alarm a ON m2.code = a.meter_code AND a.status = 'OPEN'
                    GROUP BY s3.id
                ) alarm_data ON s.id = alarm_data.station_id
                WHERE s.capacity_kva IS NOT NULL AND s.capacity_kva > 0
                {order_clause}
                LIMIT %s
            """

			results = db.get_all(sql, (limit,))
			return results or []

		except Exception as e:
			logger.error(f"获取高负载台区列表失败: {e}")
			return []

	@staticmethod
	async def get_recent_alerts(db: DatabaseService, limit: int = 20) -> List[Dict[str, Any]]:
		"""
		获取最近告警列表

		Args:
			db: 数据库服务
			limit: 返回条数

		Returns:
			List[Dict]: 告警列表
		"""
		try:
			sql = """
                SELECT
                    a.id,
                    a.meter_code,
                    a.alarm_type,
                    a.raised_at,
                    ar.severity,
                    ar.rule_name,
                    ar.threshold,
                    ar.unit,
                    ar.operator,
                    CASE ar.severity
                        WHEN 3 THEN '严重'
                        WHEN 2 THEN '一般'
                        WHEN 1 THEN '提示'
                        ELSE '未知'
                    END as severity_label,
                    -- 获取设备名称（优先台区名称）
                    COALESCE(s.name, CONCAT('电表-', a.meter_code)) as device_name,
                    CASE
                        WHEN s.name IS NOT NULL THEN '台区'
                        ELSE '电表'
                    END as device_type,
                    -- 获取当前值（从最新遥测数据）
                    CASE a.alarm_type
                        WHEN 'OVER_VOLTAGE' THEN t.voltage
                        WHEN 'UNDER_VOLTAGE' THEN t.voltage
                        WHEN 'OVER_CURRENT' THEN t.current
                        WHEN 'OVER_LOAD' THEN t.watt
                        WHEN 'HIGH_TEMP' THEN GREATEST(COALESCE(t.temperature1, 0), COALESCE(t.temperature2, 0))
                        ELSE NULL
                    END as current_value
                FROM am_alarm a
                LEFT JOIN am_alarm_rule ar ON a.alarm_type = ar.rule_code
                LEFT JOIN dev_meter m ON a.meter_code = m.code
                LEFT JOIN dev_rtu r ON m.rtu_id = r.id
                LEFT JOIN dev_box b ON r.box_id = b.id
                LEFT JOIN dev_station s ON b.station_id = s.id
                LEFT JOIN (
                    SELECT
                        meter_code,
                        voltage,
                        current,
                        watt,
                        temperature1,
                        temperature2,
                        ROW_NUMBER() OVER (PARTITION BY meter_code ORDER BY ts DESC) as rn
                    FROM dc_meter_telemetry
                    WHERE ts >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
                ) t ON a.meter_code = t.meter_code AND t.rn = 1
                WHERE a.status = 'OPEN'
                ORDER BY a.raised_at DESC
                LIMIT %s
            """

			results = db.get_all(sql, (limit,))

			# 格式化结果
			formatted_results = []
			for row in results:
				# 使用工具函数格式化告警消息
				from common.alarm_utils import get_alarm_type_label

				# 获取告警类型的中文标签
				alarm_type_label = get_alarm_type_label(row['alarm_type'])

				# 格式化告警消息
				current_val = row.get('current_value')
				threshold = row.get('threshold')
				unit = row.get('unit', '')

				# 优先使用数据库中的rule_name，其次使用中文标签
				alarm_display_name = row.get('rule_name') or alarm_type_label

				if current_val is not None and threshold is not None:
					message = f"[{row['raised_at'].strftime('%H:%M:%S')}] {row['device_name']}：发生{alarm_display_name}，当前值{current_val}{unit}，阈值{threshold}{unit}"
				else:
					message = f"[{row['raised_at'].strftime('%H:%M:%S')}] {row['device_name']}：发生{alarm_display_name}"

				formatted_results.append({
					'id': row['id'],
					'time': row['raised_at'].strftime('%H:%M:%S'),
					'device_name': row['device_name'],
					'device_type': row['device_type'],
					'alarm_type': row['alarm_type'],
					'severity': row['severity'] or 2,
					'severity_label': row['severity_label'],
					'current_value': float(current_val) if current_val else None,
					'threshold': float(threshold) if threshold else None,
					'unit': unit,
					'message': message,
					'raised_at': row['raised_at']
				})

			return formatted_results

		except Exception as e:
			logger.error(f"获取最近告警列表失败: {e}")
			return []

	@staticmethod
	async def get_alarm_type_stats(db: DatabaseService) -> List[Dict[str, Any]]:
		"""
		获取告警类型统计

		Returns:
			List[Dict]: 告警类型统计
		"""
		try:
			sql = """
                SELECT
                    ar.rule_name as alarm_type,
                    COUNT(a.id) as count
                FROM am_alarm a
                LEFT JOIN am_alarm_rule ar ON a.alarm_type = ar.rule_code
                WHERE a.status = 'OPEN'
                GROUP BY ar.rule_name
                ORDER BY count DESC
            """

			results = db.get_all(sql)
			return [
				{
					'type': row['alarm_type'] or '未知类型',
					'count': row['count']
				}
				for row in results
			]

		except Exception as e:
			logger.error(f"获取告警类型统计失败: {e}")
			return []

	@staticmethod
	async def get_hourly_alarm_trend(db: DatabaseService) -> List[Dict[str, Any]]:
		"""
		获取24小时告警趋势

		Returns:
			List[Dict]: 每小时告警数量
		"""
		try:
			sql = """
                SELECT
                    HOUR(raised_at) as hour,
                    COUNT(*) as count
                FROM am_alarm
                WHERE raised_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
                GROUP BY HOUR(raised_at)
                ORDER BY hour
            """

			results = db.get_all(sql)

			# 填充24小时数据（没有告警的小时补0）
			hourly_data = {row['hour']: row['count'] for row in results}
			trend_data = []

			for hour in range(24):
				trend_data.append({
					'hour': f"{hour:02d}:00",
					'count': hourly_data.get(hour, 0)
				})

			return trend_data

		except Exception as e:
			logger.error(f"获取24小时告警趋势失败: {e}")
			return []

	@staticmethod
	async def get_current_alarm_states(db: DatabaseService) -> List[Dict[str, Any]]:
		"""
		获取当前告警状态，用于状态变化检测

		Args:
			db: 数据库服务

		Returns:
			List[Dict]: 当前告警状态列表
		"""
		try:
			sql = """
                SELECT
                    a.id,
                    a.meter_code as device_id,
                    COALESCE(s.name, CONCAT('电表-', a.meter_code)) as device_name,
                    a.alarm_type,
                    ar.severity as alarm_level,
                    ar.rule_name as alarm_message,
                    a.raised_at,
                    a.status,
                    a.resolved_at
                FROM am_alarm a
                LEFT JOIN am_alarm_rule ar ON a.alarm_type = ar.rule_code
                LEFT JOIN dev_meter m ON a.meter_code = m.code
                LEFT JOIN dev_rtu r ON m.rtu_id = r.id
                LEFT JOIN dev_box b ON r.box_id = b.id
                LEFT JOIN dev_station s ON b.station_id = s.id
                WHERE a.raised_at >= DATE_SUB(NOW(), INTERVAL 2 HOUR)
                ORDER BY a.raised_at DESC
            """

			results = db.get_all(sql)

			alarm_states = []
			for row in results:
				# 使用工具函数获取中文标签
				from common.alarm_utils import get_alarm_type_label
				alarm_type_label = get_alarm_type_label(row['alarm_type'])

				alarm_states.append({
					"id": row["id"],
					"device_id": row["device_id"],
					"device_name": row["device_name"],
					"alarm_type": row["alarm_type"],
					"alarm_level": row["alarm_level"] or 2,
					"alarm_message": row["alarm_message"] or alarm_type_label,  # 优先使用rule_name，其次使用中文标签
					"raised_at": row["raised_at"].isoformat() if row["raised_at"] else None,
					"resolved_at": row["resolved_at"].isoformat() if row["resolved_at"] else None,
					"status": "active" if row["status"] == "OPEN" else "resolved"
				})

			return alarm_states

		except Exception as e:
			logger.error(f"获取当前告警状态失败: {e}")
			return []
